#!/usr/bin/python
# -*- coding: utf-8 -*-

class Tov():
    
    def __init__(self, orderNomer, tovDate, tovTime, opakowka, serviceSpeed, atAccountOF, paymentType, \
                 obrDocs, obrRazpiska, onDemand, packDescription, fromOffice, toOffice, senderName, senderStreet, \
                 senderPhone, receiverName, receiverStreet, receiverPhone, packCount, packWeight, nalojenPlatej, \
                 feeNalPlatej, fixedTimeFeeQ, courierFee, courierFeeInclVAT, total):
        self.orderNomer = orderNomer    # Number
        self.tovDate = tovDate          # Date/Time
        self.tovTime = tovTime          # Date/Time
        self.opakowka = opakowka        # Text
        self.serviceSpeed = serviceSpeed    # Text
        self.atAccountOF = atAccountOF      # Text
        self.paymentType = paymentType      # Text
        self.obrDocs = obrDocs              # Yes/No
        self.obrRazpiska = obrRazpiska      # Yes/No
        self.onDemand = onDemand            # Yes/No
        self.packDescription = packDescription  # Text
        self.fromOffice = fromOffice            # Text
        self.toOffice = toOffice                # Text
        self.senderName = senderName            # Text
        self.senderStreet = senderStreet        # Text
        self.senderPhone = senderPhone          # Text
        self.receiverName = receiverName        # Text
        self.receiverStreet = receiverStreet    # Text
        self.receiverPhone = receiverPhone      # Text
        self.packCount = packCount              # Number
        self.packWeight = packWeight            # Number
        self.nalojenPlatej = nalojenPlatej      # Number
        self.feeNalPlatej = feeNalPlatej        # Number
        self.fixedTimeFeeQ = fixedTimeFeeQ      # Number
        self.courierFee = courierFee            # Number
        self.courierFeeInclVAT = courierFeeInclVAT  # Number
        self.total = total                          # Number
        
        ## total 27 record positions
        
    def getDataAsDict(self):
        tovHeaders = ["orderNomer", "tovDate", "tovTime", "opakowka", "serviceSpeed", \
                          "atAccountOF", "paymentType", "obrDocs", "obrRazpiska", "onDemand", \
                          "packDescription", "fromOffice", "toOffice", "senderName", "senderStreet", \
                          "senderPhone", "receiverName", "receiverStreet", "receiverPhone", "packCount", \
                          "packWeight", "nalojenPlatej", "feeNalPlatej", "fixedTimeFeeQ", "courierFee", \
                          "courierFeeInclVAT", "total"]
        
        tovData = [self.orderNomer, self.tovDate, self.tovTime, self.opakowka, \
                self.serviceSpeed, self.atAccountOF, self.paymentType, self.obrDocs, \
                self.obrRazpiska, self.onDemand, self.packDescription, self.fromOffice, \
                self.toOffice, self.senderName, self.senderStreet, self.senderPhone, \
                self.receiverName, self.receiverStreet, self.receiverPhone, self.packCount, \
                self.packWeight, self.nalojenPlatej, self.feeNalPlatej, self.fixedTimeFeeQ, \
                self.courierFee, self.courierFeeInclVAT, self.total]
        
        return dict(zip(tovHeaders, tovData))
        
    def toString(self):
        return self.orderNomer+", "+ self.tovDate+", "+ self.tovTime+", "+ self.opakowka+", "+\
                self.serviceSpeed+", "+ self.atAccountOF+", "+ self.paymentType+", "+ self.obrDocs+", "+ \
                self.obrRazpiska+", "+ self.onDemand+", "+ self.packDescription+", "+ self.fromOffice+", "+ \
                self.toOffice+", "+ self.senderName+", "+ self.senderStreet+", "+ self.senderPhone+", "+ \
                self.receiverName+", "+ self.receiverStreet+", "+ self.receiverPhone+", "+ self.packCount+", "+ \
                self.packWeight+", "+ self.nalojenPlatej+", "+ self.feeNalPlatej+", "+ self.fixedTimeFeeQ+", "+ \
                self.courierFee+", "+ self.courierFeeInclVAT+", "+ self.total
 
            

class Address():
    def __init__(self, street, postcode, streetNomer, block, vhod, appartment, floor, country="BULGARIA", town="София"):
        self.country = country
        self.town = town
        self.street = street
        self.postcode = postcode
        self.streetNomer = streetNomer
        self.block = block
        self.vhod = vhod
        self.appartment = appartment
        self.floor = floor
    
    def getAddress(self):
        adr = self.street
        if self.streetNomer != "":
            adr += " "+self.streetNomer
        if self.block != "":
            adr += "\n Bl. "+self.block
        if self.vhod != "":
            adr += " Entr. "+self.vhod
        if self.floor != "":
            adr += " et. "+self.floor
        if self.appartment != "":
            adr += " ap. "+self.appartment
        return adr # string
    
    def toString(self):
        return self.country+", "+self.town+", "+self.street+", "+self.postcode+", "+\
        self.streetNomer+", "+self.block+", "+self.vhod+", "+self.appartment+", "+self.floor
    
class Tarifa():
    
    kilaSf = [0.5, 5.0, 10.0, 15.0, 20.0, 50.0]
    ceniSfStandard = [3.0, 4.0, 6.0, 8.0, 12.0, 20.0]
    ceniSfFast = [4.0, 6.0, 8.0, 10.0, 14.0, 23.0]
    ceniSfExpress = [5.0, 9.0, 10.0, 14.0, 21.0, 35.0]
    ceniSfSunday = [18.0, 20.0, 30.0, 30.0, 30.0, 30.0]
    ceniSfAfter1930 = [20.0, 20.0]
    
    kilaCntry = [0.5, 5.0, 10.0, 15.0, 20.0]
    ceniCntry = [4.0, 5.0, 8.0, 10.0, 12.0]
        
    def __init__(self):
        self.update()
        
    def update(self):
        standard = dict(zip(self.kilaSf, self.ceniSfStandard))
        fast = dict(zip(self.kilaSf, self.ceniSfFast))
        express = dict(zip(self.kilaSf, self.ceniSfExpress))
        sunday = dict(zip(self.kilaSf, self.ceniSfSunday))
        after1930 = dict(zip(self.kilaSf, self.ceniSfAfter1930))
        sofia = {0:standard, 1:fast, 2:express, 'sunday':sunday, 'late':after1930}
        standard3 = dict(zip(self.kilaCntry, self.ceniCntry))
        countryside = {0:standard3}
        self.td = {'sofia':sofia, 'countryside':countryside}
        self.extraKG = {'sofia':0.4, 'countryside':0.5}
        self.mags = 0.026   
   
    
class Client():
    def __init__(self, clid, ime, prezime, familia, egneik, telefon, notes, tarifa=None):
        self.clid = clid
        self.ime = ime
        self.prezime = prezime
        self.familia = familia
        self.egneik = egneik
        self.telefon = telefon
        self.notes = notes
        self.tarifa = None
        self.address = None
        
    def setTarifa(self, tarifa):
        self.tarifa = tarifa
    
    def setAddress(self, country, town, street, postcode, streetNomer, block, vhod, appartment, floor):
        self.address = Address(street, postcode, streetNomer, block, vhod, appartment, floor, country, town)
        
    def getFullName(self):
        return self.ime+" "+self.familia
    
    def toString(self):
        return self.clid+", "+self.ime+", "+self.familia+", "+self.egneik+", "+\
        self.telefon+", "+self.notes+", "+self.address.toString()+", "+str(self.tarifa)
    
from PyQt4 import QtCore, QtSql, QtGui

class DB():
    def __init__(self):
        ## initialise db - ms access file
        self.db = QtSql.QSqlDatabase.addDatabase("QODBC")
        self.db.setDatabaseName("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=bo.mdb")
        
    def getClients(self):
        clients = QtCore.QStringList()
        cl = {}
        if (self.db.open()):
            # success
            query = QtSql.QSqlQuery()
            query.exec_("SELECT klienti.*, Address.* FROM klienti INNER JOIN Address ON klienti.ID = Address.clientID")
            while query.next():
                clid, ok = query.value(0).toInt()
                aClient = Client(clid,\
                                QtGui.QApplication.translate("dedo", query.value(1).toString()),\
                                QtGui.QApplication.translate("dedo", query.value(2).toString()),\
                                QtGui.QApplication.translate("dedo", query.value(3).toString()),\
                                QtGui.QApplication.translate("dedo", query.value(4).toString()),\
                                QtGui.QApplication.translate("dedo", query.value(5).toString()),\
                                QtGui.QApplication.translate("dedo", query.value(6).toString()))
                
                aClient.setAddress(self.toCp1251(query.value(9).toString()), self.toCp1251(query.value(10).toString()), \
                                    self.toCp1251(query.value(11).toString()), self.toCp1251(query.value(12).toString()), \
                                    self.toCp1251(query.value(13).toString()),self.toCp1251(query.value(14).toString()),\
                                    self.toCp1251(query.value(15).toString()), self.toCp1251(query.value(16).toString()), \
                                    self.toCp1251(query.value(17).toString()))
                clients.append(aClient.getFullName()+" "+str(aClient.clid))
                aTarif = Tarifa()
                aClient.setTarifa(aTarif)
                cl[aClient.clid] = aClient
            self.db.close()
            print "select klienti successful"
            
            return clients, cl
        else:
            print 'db openning problem'
            return None
    
    
    def getTovs(self):
        tovs = []
        if (self.db.open()):
            # success
            query = QtSql.QSqlQuery()
            query.exec_("SELECT * FROM tov")
            
            while query.next():
                aTov = Tov(self.toCp1251(query.value(0).toString()), self.toCp1251(query.value(1).toString()), self.toCp1251(query.value(2).toString()), self.toCp1251(query.value(3).toString()), \
                           self.toCp1251(query.value(4).toString()), self.toCp1251(query.value(5).toString()), self.toCp1251(query.value(6).toString()), self.toCp1251(query.value(7).toString()), \
                            self.toCp1251(query.value(8).toString()), self.toCp1251(query.value(9).toString()), self.toCp1251(query.value(10).toString()), self.toCp1251(query.value(11).toString()), \
                            self.toCp1251(query.value(12).toString()), self.toCp1251(query.value(13).toString()), self.toCp1251(query.value(14).toString()), self.toCp1251(query.value(15).toString()), \
                            self.toCp1251(query.value(16).toString()), self.toCp1251(query.value(17).toString()), self.toCp1251(query.value(18).toString()), self.toCp1251(query.value(19).toString()), \
                            self.toCp1251(query.value(20).toString()), self.toCp1251(query.value(21).toString()), self.toCp1251(query.value(22).toString()), self.toCp1251(query.value(23).toString()), \
                            self.toCp1251(query.value(24).toString()), self.toCp1251(query.value(25).toString()), self.toCp1251(query.value(26).toString()))
                tovs.append(aTov)
            self.db.close()
            print "select tovs successful"
            return tovs
        else:
            print 'db openning problem'
            return None
        
    def getServiceSpeed(self):
        serviceSpeed = QtCore.QStringList()
        if (self.db.open()):
            # success
            query = QtSql.QSqlQuery()
            query.exec_("SELECT serviceType FROM serviceType")
            
            while query.next():
                serviceSpeed.append(self.toCp1251(query.value(0).toString()))
            self.db.close()
            print "service speed types loaded."
            return serviceSpeed
        else:
            print 'db openning problem'
            return None

    def getDefaultConfig(self):
        defConfig = {}
        
        if (self.db.open()):
            # success
            query = QtSql.QSqlQuery()
            query.exec_("SELECT key, value FROM config")
            
            while query.next():
                key = self.toCp1251(query.value(0).toString())
                val, ok = query.value(1).toDouble()
                if ok:
                    defConfig[key] = val
            self.db.close()
            print "default rates loaded."
            return defConfig
        else:
            print 'db openning problem'
            return None
        
    def getDefaultTarif(self):
        defTarif = {}
        
        if (self.db.open()):
            # success
            query = QtSql.QSqlQuery()
            query.exec_("SELECT * FROM defaultTarif")
            
            while query.next():
                kila = []
                ceni = []
                where = ''
                speed = ''
                first = query.value(0).toString()
                if first == 'sofia' or first == 'countryside':
                    where = first
                    for i in range(1, 6):
                        val, ok = query.value(i).toDouble()
                        if ok:
                            kila.append(val)
                else:
                    speed = first
                    for i in range(1, 6):
                        val, ok = query.value(i).toDouble()
                        if ok:
                            ceni.append(val)
                    speed = dict(zip(kila, ceni))                     
                defTarif[where] = speed
                
            self.db.close()
            print "default rates loaded."
            return defConfig
        else:
            print 'db openning problem'
            return None
        
    def toCp1251(self, string):
        return unicode(string, 'cp1251')
        
    def insertTovInDBase(self, tov):
        if not tov:
            print 'kur na inserta w tov table!'
            return
        if (self.db.open()):
            query = QtSql.QSqlQuery()
            
            '''
            tovRecords = ["orderNomer", "tovDate", "tovTime", "opakowka", "serviceSpeed", \
                          "atAccountOF", "paymentType", "obrDocs", "obrRazpiska", "onDemand", \
                          "packDescription", "fromOffice", "toOffice", "senderName", "senderStreet", \
                          "senderPhone", "receiverName", "receiverStreet", "receiverPhone", "packCount", \
                          "packWeight", "nalojenPlatej", "feeNalPlatej", "fixedTimeFeeQ", "courierFee", \
                          "courierFeeInclVAT", "total"]
            
            count = len(tovRecords)
            insertStatement = '"INSERT INTO tov('
            for rec in tovRecords:
                insertStatement += rec
                insertStatement += ', '
            insertStatement += ') VALUES ('+('?, '*count)+')"'
            
            query.prepare(insertStatement)
            '''
            query.prepare("INSERT INTO tov(orderNomer, tovDate, tovTime, opakowka, serviceSpeed, \
                          atAccountOF, paymentType, obrDocs, obrRazpiska, onDemand, packDescription, \
                          fromOffice, toOffice, senderName, senderStreet, senderPhone, \
                          receiverName, receiverStreet, receiverPhone, \
                          packCount, packWeight, nalojenPlatej, feeNalPlatej, fixedTimeFeeQ, \
                          courierFee, courierFeeInclVAT, total) \
                          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
            
            
            query.bindValue(0, tov.orderNomer)
            query.bindValue(1, tov.tovDate)
            query.bindValue(2, tov.tovTime)
            query.bindValue(3, tov.opakowka)
            query.bindValue(4, tov.serviceSpeed)
            query.bindValue(5, tov.atAccountOF)
            query.bindValue(6, tov.paymentType)
            query.bindValue(7, tov.obrDocs)
            query.bindValue(8, tov.obrRazpiska)
            query.bindValue(9, tov.onDemand)
            query.bindValue(10, tov.packDescription)
            query.bindValue(11, tov.fromOffice)
            query.bindValue(12, tov.toOffice)
            query.bindValue(13, tov.senderName)
            query.bindValue(14, tov.senderStreet)
            query.bindValue(15, tov.senderPhone)
            query.bindValue(16, tov.receiverName)
            query.bindValue(17, tov.receiverStreet)
            query.bindValue(18, tov.receiverPhone)
            query.bindValue(19, tov.packCount)
            query.bindValue(20, tov.packWeight)
            query.bindValue(21, tov.nalojenPlatej)
            query.bindValue(22, tov.feeNalPlatej)
            query.bindValue(23, tov.fixedTimeFeeQ)
            query.bindValue(24, tov.courierFee)
            query.bindValue(25, tov.courierFeeInclVAT)
            query.bindValue(26, tov.total)
            rezult = query.exec_()
            self.db.close()
            return rezult
        else:
            return
        
        
    def insertClientInDBase(self, client):
        if not client:
            print 'kur na inserta w klienti table!'
            return
        if (self.db.open()):
            query = QtSql.QSqlQuery()
            
            query.prepare("INSERT INTO klienti(ime, prezime, familia, egneik, telefon, notes, tarifa) \
                          VALUES (?, ?, ?, ?, ?, ?, ?)")
            
            ## clid, ime, prezime, familia, egneik, telefon, notes, tarifa=None
            #query.bindValue(0, client.clid)
            query.bindValue(0, client.ime)
            query.bindValue(1, client.prezime)
            query.bindValue(2, client.familia)
            query.bindValue(3, client.egneik)
            query.bindValue(4, client.telefon)
            query.bindValue(5, client.notes)
            query.bindValue(6, client.tarifa)
            
            rezult = query.exec_()
            self.db.close()
            return rezult
        else:
            return

        
        
########################################################################
class TovPlainDoc(QtGui.QTextDocument):
    """
    Clas that holds Tovs data, for viewing and printing
    """

    #----------------------------------------------------------------------
    def __init__(self, tov):
        """Constructor"""
        super(TovPlainDoc, self).__init__()
        self.buildTovDoc(tov)
        
    #----------------------------------------------------------------------    
    def buildTovDoc(self, tov):
        """
        actualy who do the stuff
        """
        
        cursor = QtGui.QTextCursor(self)        
        cursor.movePosition(QtGui.QTextCursor.Start)
        topFrame = cursor.currentFrame()
        topFrameFormat = topFrame.frameFormat()
        topFrameFormat.setPadding(16)
        topFrame.setFrameFormat(topFrameFormat)

        textFormat = QtGui.QTextCharFormat()
        boldFormat = QtGui.QTextCharFormat()
        boldFormat.setFontWeight(QtGui.QFont.Bold)

        tovTable = cursor.insertTable(3, 3)
        tovTableFormat = QtGui.QTextTableFormat()
        #tovTableFormat.setBorderStyle(QtGui.QTextFrameFormat.BorderStyle_None)
        #tovTableFormat.setWidth(self.width()/3)
        #tovTableFormat.setHeight(self.height()/3)
        tovTable.setFormat(tovTableFormat)
        
        detailsTableFormat = QtGui.QTextTableFormat()
        detailsTableFormat.setBorderStyle(QtGui.QTextFrameFormat.BorderStyle_None)

        cursor = tovTable.cellAt(0, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("ПАРКУР ООД"), textFormat)
        cursor.insertBlock()
        cursor.insertText(self.trUtf8("Поръчка номер: ")+tov.orderNomer, textFormat)
        cursor.insertBlock()
        cursor.insertText(self.trUtf8("Опаковка: ")+tov.opakowka, textFormat)
        
        ## Sender Details
        cursor = tovTable.cellAt(1, 0).firstCursorPosition()
        senderDetails = cursor.insertTable(3, 1)
        senderDetails.setFormat(detailsTableFormat)
        cursor = senderDetails.cellAt(0, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("Направление:\n от офис ")+tov.fromOffice, textFormat)
        
        cursor = senderDetails.cellAt(1, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("Подател:\n")+tov.senderName, textFormat)
        
        cursor = senderDetails.cellAt(2, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("Адрес:\n")+tov.senderStreet, textFormat)
        
        ## Sender Bottom
        cursor = tovTable.cellAt(2, 0).firstCursorPosition()
        senderDetailsBottom = cursor.insertTable(2, 2)
        cursor = senderDetailsBottom.cellAt(0, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("Телефон:\n")+tov.senderPhone, textFormat)

        cursor = senderDetailsBottom.cellAt(1, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("Куриер:\n"), textFormat)
        
        cursor = senderDetailsBottom.cellAt(0, 1).firstCursorPosition()
        cursor.insertText(self.trUtf8("Подпис:\n"), textFormat)

        cursor = senderDetailsBottom.cellAt(1, 1).firstCursorPosition()
        cursor.insertText(self.trUtf8("Дата: ")+tov.tovDate+self.trUtf8("\nЧас: ")+tov.tovTime, textFormat)
        
        ## Receiver details
        cursor = tovTable.cellAt(1, 1).firstCursorPosition()
        receiverDetails = cursor.insertTable(3, 1)
        receiverDetails.setFormat(detailsTableFormat)
        cursor = receiverDetails.cellAt(0, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("\n за офис ")+tov.toOffice, textFormat)

        cursor = receiverDetails.cellAt(1, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("Получател:\n")+tov.receiverName, textFormat)
        
        cursor = receiverDetails.cellAt(2, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("Адрес:\n")+tov.receiverStreet, textFormat)
        
        ## Receiver Bottom
        cursor = tovTable.cellAt(2, 1).firstCursorPosition()
        receiverDetailsBottom = cursor.insertTable(2, 2)
        cursor = receiverDetailsBottom.cellAt(0, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("Телефон:\n")+tov.receiverPhone, textFormat)
        
        cursor = receiverDetailsBottom.cellAt(0, 1).firstCursorPosition()
        cursor.insertText(self.trUtf8("Приел пратката:\n"), textFormat)
        
        cursor = receiverDetailsBottom.cellAt(1, 0).firstCursorPosition()
        cursor.insertText(self.trUtf8("Куриер:\n"), textFormat)
        
        cursor = receiverDetailsBottom.cellAt(1, 1).firstCursorPosition()
        cursor.insertText(self.trUtf8("Дата: ")+tov.tovDate+self.trUtf8("\nЧас: ")+tov.tovTime, textFormat)
        
        ## Financials Details
        
        cursor = tovTable.cellAt(0, 2).firstCursorPosition()
        cursor.insertText(self.trUtf8("Тегло: до ") + tov.packWeight+ self.trUtf8(" кг."), textFormat)
        cursor.insertBlock()
        cursor.insertBlock()
        cursor.insertText(self.trUtf8("Брой пакети: ") + tov.packCount, textFormat)
        
        cursor = tovTable.cellAt(1, 2).firstCursorPosition()
        cursor.insertText(self.trUtf8("Наложен платеж: ") + tov.nalojenPlatej+ self.trUtf8(" лв."), textFormat)
        cursor.insertBlock()
        cursor.insertBlock()
        cursor.insertText(self.trUtf8("Такса наложен платеж: ") + tov.feeNalPlatej + self.trUtf8(" лв."), textFormat)
        cursor.insertBlock()
        cursor.insertBlock()
        cursor.insertText(self.trUtf8("Добавка фиксиран час: ") + tov.fixedTimeFeeQ + self.trUtf8(" лв."), textFormat)
        
        cursor = tovTable.cellAt(2, 2).firstCursorPosition()
        cursor.insertText(self.trUtf8("Куриерска услуга: ") + tov.courierFee + self.trUtf8(" лв."), textFormat)
        cursor.insertBlock()
        cursor.insertBlock()
        cursor.insertText(self.trUtf8("с ДДС: ") + tov.courierFeeInclVAT + self.trUtf8(" лв."), textFormat)
        cursor.insertBlock()
        cursor.insertBlock()
        cursor.insertText(self.trUtf8("Общо: ") + tov.total + self.trUtf8(" лв."), textFormat)   
        
        
        
